if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MapChangedAgreementRows]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[MapChangedAgreementRows]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



/***************************************************************************
* Name:			MapChangedAgreementRows
* Purpose:		Sets a 'HasChanged' flag on the Staging table to determine if a field
*				on that agreement has changed since the last time the feed was run.
*
***************************************************************************/
CREATE PROCEDURE dbo.MapChangedAgreementRows
AS
BEGIN

	SET NOCOUNT ON

	BEGIN
		Update Stg_IE_AgrAgreement Set HasChanged = 1 WHERE
		AgrAgreementId is not null AND (
			(ISNULL(PIPersonId, 0) != ISNULL(ExistingPIPersonId, 0)) OR
			(ISNULL(ProjectStartDate, '') != ISNULL(ExistingProjectStartdate, '')) OR
			(ISNULL(ProjectEndDate, '') != ISNULL(ExistingProjectEndDate, '')) OR
			(ExistingAgreementTitle != AgreementTitle) OR
			(ExistingAgreementShortTitle != AgreementShortTitle) OR
			(ISNULL(ExistingReceivedDate, '') != ISNULL(ReceivedDate, '')) OR
			(ISNULL(ExistingLegacyIdentifier, '') != ISNULL(LegacyIdentifier, '')) OR
			(ISNULL(ParentFolderNumber, '') != ISNULL(ExistinParentFolderNumber, '')) OR
			(ISNULL(RFAID, '') != ISNULL(ExistingRFAId, '') ) OR
			(ISNULL(RFATitle, '') != ISNULL(ExistingRFATitle, '') ) OR
			(ISNULL(RFATitle, '') != ISNULL(ExistingRFATitle, '') ) OR
			(ISNULL(Status, '') != ISNULL(ExistingStatus, '') ) OR
			(ISNULL(AgrAgreementTypeId, 0) != ISNULL(ExistingAgreementTypeId, 0)) OR 
			(ISNULL(AdmSponsorId, 0) != ISNULL(ExistingAdmSponsorId, 0)) OR 
			(ISNULL(AgrAgreementStatusTypeId, 0) != ISNULL(ExistingAgrAgreementStatusTypeId, 0 ))
		)	
	END
		
	SET NOCOUNT OFF

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO